package dao;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import common.Constant;
import model.MonHocModel;
import model.SinhVienModel;
import util.DataUtil;
import util.StringUtil;

public class MonHocDAO {
	public static ArrayList<MonHocModel> getAllMonHocByTen(int indexStart,int total,String tenMH)
	{
		ArrayList<MonHocModel> monHocListModel=new ArrayList<MonHocModel>();
		int rsTotalPage=0;

		if (indexStart > 1)
	     {			 
	         indexStart = (indexStart * total) - (total -1);
	         rsTotalPage=total+indexStart-1;	         
	     }
		 else if (indexStart == 1)
	         rsTotalPage=total;

		try {

			CallableStatement csmt = DataUtil
				.getConnection()
				.prepareCall("{call sp_MonHoc_GetMonHocByTen(?,?,?)}");
			csmt.setInt("IndexStart",indexStart);
			csmt.setInt("Total",rsTotalPage);
			csmt.setNString("TenMH","%"+tenMH+"%");
			
			ResultSet rs = DataUtil.executeStore(csmt);

			MonHocModel monHocModel;
			while(rs.next())
			{
				monHocModel=new MonHocModel();
				monHocModel.setStt(indexStart+"");
				monHocModel.setMaMH(rs.getString("MaMH"));
				monHocModel.setTenMH(rs.getNString("Ten_MH"));
				monHocModel.setThoiGianBatDau(rs.getString("Thoi_Gian_Bat_Dau"));
				monHocModel.setThoiGianKetThuc(rs.getString("Thoi_Gian_Ket_Thuc"));
				monHocModel.setChungChi(rs.getString("Chung_Chi"));
				monHocModel.setLePhi(rs.getString("Le_Phi"));
				monHocModel.setTongSVDK(rs.getString("TongSVDK"));
				monHocListModel.add(monHocModel);
				indexStart++;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return monHocListModel;
	}

	public static int getTotalMonHocByTen(String tenMH)
	{
		try {
			CallableStatement csmt = DataUtil
				.getConnection()
				.prepareCall("{call sp_MonHoc_GetTotalMonHocByTen(?)}");
			
			csmt.setNString("TenMH","%"+tenMH+"%");
			
			ResultSet rs = DataUtil.executeStore(csmt);
			
			while(rs.next())
			{
				return rs.getInt("Total"); 
			}
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
		return 0;
	}
	
	public static ArrayList<String> showNumPage(int i,int total,String tenFile)
	{
		String str="";
		ArrayList<String> strList=new ArrayList<String>();
		int j=i;
         if (i > 1)
             i = i - 1;
         
         
             while (i <= (total) / Constant.NUM_RECORD_MonHoc)
             {
            	 if(i==j)
            		 str="<a href='"+tenFile+"?index=" + i + "' style='color:blue;background-color:yellow;text-decoration:none'>"+i+"</a>";
            	 else
            		 str="<a href='"+tenFile+"?index=" + i + "' style='color:blue;text-decoration:none'>"+i+"</a>";
                 strList.add(str);                 
            	 i++;
             }
             if ( total % Constant.NUM_RECORD_MonHoc != 0 && total > Constant.NUM_RECORD_MonHoc)
             {
            	 if(i==j)
            		 str="<a href='"+tenFile+"?index=" + i + "' style='color:blue;background-color:yellow;text-decoration:none'>"+i+"</a>";
            	 else
            		 str="<a href='"+tenFile+"?index=" + i + "' style='color:blue;text-decoration:none'>"+i+"</a>";
                 strList.add(str);
             }

		return strList;
	}
	
	public static Boolean insertMonHoc(MonHocModel monHocModel){
		try {
			CallableStatement csmt = DataUtil
				.getConnection()
				.prepareCall("{call sp_MonHoc_InsertMonHoc(?,?,?,?,?,?)}");
			csmt.setNString("MaMH",monHocModel.getMaMH());
			csmt.setNString("Ten_MH",monHocModel.getTenMH());
			csmt.setNString("Thoi_Gian_Bat_Dau",monHocModel.getThoiGianBatDau());
			csmt.setNString("Thoi_Gian_Ket_Thuc",monHocModel.getThoiGianKetThuc());
			csmt.setNString("Chung_Chi",monHocModel.getChungChi());
			csmt.setString("Le_Phi",monHocModel.getLePhi());
	
			return DataUtil.executeNonStore(csmt);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		
	}
	
	
	public static Boolean updateMonHocByMaMH(MonHocModel monHocModel){
		
		try {
			CallableStatement csmt = DataUtil
				.getConnection()
				.prepareCall("{call sp_MonHoc_UpdateMonHocByMaMH(?,?,?,?,?,?)}");
			csmt.setString("MaMH",monHocModel.getMaMH());
			csmt.setNString("Ten_MH",monHocModel.getTenMH());
			csmt.setNString("Thoi_Gian_Bat_Dau",monHocModel.getThoiGianBatDau());
			csmt.setNString("Thoi_Gian_Ket_Thuc",monHocModel.getThoiGianKetThuc());
			csmt.setNString("Chung_Chi",monHocModel.getChungChi());
			csmt.setString("Le_Phi",monHocModel.getLePhi());
	
			return DataUtil.executeNonStore(csmt);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public static Boolean deleteMonHocByMaMH(String maMonHoc){
		Boolean result = false;
		try {
			CallableStatement csmt = DataUtil.getConnection().prepareCall("{sp_MonHoc_DeleteMonHocByMaMH(?)}");
			csmt.setString("MaMH", maMonHoc);
			result = DataUtil.executeNonStore(csmt);
		} catch (Exception e) {
			e.printStackTrace();
		}		
		
	return result;
	}
	public static Boolean deleteMonHoc(String maMonHoc) {
		Boolean result = false;		
		try {
			CallableStatement csmt = DataUtil
				.getConnection()
				.prepareCall("{call sp_MonHoc_DeleteMonHoc(?)}");
			
			csmt.setString("MaMH",maMonHoc);
			
			result = DataUtil.executeNonStore(csmt);			
		} catch (Exception e) {
			e.printStackTrace();
		}						
		return result;
	}
	
	public static ArrayList<MonHocModel> showAllMonHoc() {
		ArrayList<MonHocModel> listKetQua = new ArrayList<MonHocModel>();
		try {
			PreparedStatement preparedStatement = DataUtil.getConnection()
					.prepareStatement(
							Constant.SQL_RES.getString("sql.getAllMonHoc"));
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				MonHocModel monHocModel = new MonHocModel();
				monHocModel.setMaMH(rs.getString("MaMonHoc"));
				monHocModel.setTenMH(rs.getString("TenMonHoc"));
				
				listKetQua.add(monHocModel);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listKetQua;
	}
	
	/*chi tiet mon hoc*/
	public static MonHocModel XemChiTietMonHocByMaMonHoc(String maMonHoc) {
		MonHocModel monHocModel = new MonHocModel();
		try {
			PreparedStatement preparedStatement = DataUtil
					.getConnection()
					.prepareStatement(
							Constant.SQL_RES
									.getString("sql.showChiTietMonHocByMaMonHoc"));
			preparedStatement.setString(1, maMonHoc);			
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				monHocModel.setMaMH(rs.getString("MaMonHoc"));
				monHocModel.setTenMH(rs.getString("TenMonHoc"));
				monHocModel.setChungChi(rs.getString("ChungChi"));
				monHocModel.setLePhi(rs.getString("LePhi"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return monHocModel;
	}
	
	//dang ky
	public static ArrayList<MonHocModel> getAllMonHoc() {
		ArrayList<MonHocModel> listKetQua = new ArrayList<MonHocModel>();
		try {
			PreparedStatement preparedStatement = DataUtil.getConnection()
					.prepareStatement(
							Constant.SQL_RES.getString("sql.getAllMonHoc"));
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				MonHocModel monHocModel = new MonHocModel();
				
				monHocModel.setMaMH(rs.getString("MaMonHoc"));
				monHocModel.setTenMH(rs.getString("TenMonHoc"));
				monHocModel.setChungChi(rs.getString("ChungChi"));
				monHocModel.setLePhi(rs.getString("LePhi"));
				
				listKetQua.add(monHocModel);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listKetQua;
	}
}
